package com.yq.core.util;

import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import freemarker.template.Configuration;
import freemarker.template.Template;

import java.io.*;
import java.sql.*;
import java.util.List;
import java.util.Map;

/**
 * 导出Word
 */

public class ExportWord {

    private Configuration configuration = null;

    public ExportWord() {
        configuration = new Configuration();
        configuration.setDefaultEncoding("UTF-8");
    }

    public void createWord(String tables) throws Exception {
        Map<String, Object> dataMap = Maps.newHashMap();

        configuration.setDirectoryForTemplateLoading(new File("E:/"));//模板文件所在路径
        Template t = configuration.getTemplate("word_tmpl.xml"); //获取模板文件
        File outFile = new File("E:/tables/tables.xml"); //导出文件
        Writer out = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(outFile)));

        dataMap.put("tables", tables);

        t.process(dataMap, out); //将填充数据填入模板文件并输出到目标文件
    }

    public List<Map<String, Object>> getSQL(String sql) throws Exception {
        String URL = "jdbc:mysql://rm-2ze3nyqkl492o8l8xo.mysql.rds.aliyuncs.com:3306/cxtxdbdev?zeroDateTimeBehavior=convertToNull&useUnicode=true&characterEncoding=utf8";
        String USER = "cxtxdev";
        String PASSWORD = "Admin@cxtxDB";

        //1.加载驱动程序
        Class.forName("com.mysql.jdbc.Driver");

        //2.获得数据库链接
        Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);

        //3.通过数据库的连接操作数据库，实现增删改查（使用Statement类）
        Statement st = conn.createStatement();
        ResultSet rs = st.executeQuery(sql);

        List<Map<String, Object>> list = Lists.newArrayList();
        ResultSetMetaData rsm = rs.getMetaData(); //获得列集

        //4.处理数据库的返回结果(使用ResultSet类)
        while (rs.next()) {
            Map<String, Object> obj = Maps.newConcurrentMap();
            for (int i = 1; i < rsm.getColumnCount() + 1; i++) {
                String name = rsm.getColumnName(i);
                obj.put(name, rs.getString(name));
            }
            list.add(obj);
        }

        //关闭资源
        rs.close();
        st.close();
        conn.close();

        return list;
    }

    public String readFileByChars(String fileName) throws Exception {
        File file = new File(fileName);
        StringBuffer str = new StringBuffer();

        // 一次读一个字符
        Reader reader = new InputStreamReader(new FileInputStream(file));
        int tempchar;
        while ((tempchar = reader.read()) != -1) {
            if (((char) tempchar) != '\r') {
                str.append((char)tempchar);
            }
        }
        reader.close();

        return str.toString();
    }

    public String tableLieTmpl(String lieStrTmpl, Map<String, Object> t, int number) {

        System.out.println(t);

        String lieStr = new String(lieStrTmpl);
        lieStr = lieStr.replaceAll("lieming", t.get("COLUMN_NAME").toString());
        lieStr = lieStr.replaceAll("leixing", t.get("COLUMN_TYPE").toString());
        lieStr = lieStr.replaceAll("kong", t.get("IS_NULLABLE").toString().equals("NO")?"非空":"");
        lieStr = lieStr.replaceAll("shuoming", t.get("COLUMN_COMMENT").toString());
        lieStr = lieStr.replaceAll("zhujian", number==0?"主键":"");

        return lieStr;
    }

    public String tableTmpl(String strTmpl, Map<String, Object> t, int number) {

        //System.out.println(t);
        String str = new String(strTmpl);
        str = str.replaceAll("table_number", (number+1)+"");
        str = str.replaceAll("table_name", t.get("TABLE_NAME").toString());
        str = str.replaceAll("shuoming", t.get("TABLE_COMMENT").toString());
        str = str.replaceAll("table_lie_tmpl", t.get("table_lie_tmpl").toString());

        return str;
    }


    public static void main(String[] args) throws Exception {
        ExportWord ew = new ExportWord();
        List<Map<String, Object>> list = ew.getSQL("SELECT TABLE_NAME,TABLE_COMMENT FROM information_schema.TABLES WHERE table_schema='cxtxdbdev'");

        // ${lieming} ${leixing} ${kong} ${shuoming} ${zhujian}
        String table_lie_tmpl = ew.readFileByChars("E:/table_lie_tmpl.xml");

        // ${table_number} ${table_name} ${shuoming} ${table_lie_tmpl}
        String table_tmpl = ew.readFileByChars("E:/table_tmpl.xml");

        StringBuffer tablesb = new StringBuffer();
        for (int i = 0; i < list.size(); i++) {
            //if(i==99 ) {
                //System.out.println(list.get(i));
            //}
           // if(i>=150 && i<=200 ) {
                Map<String, Object> obj = list.get(i);

                //System.out.println(obj.get("TABLE_NAME") + "  " + obj.get("TABLE_COMMENT"));
                List<Map<String, Object>> tables = ew.getSQL("SELECT COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, COLUMN_COMMENT FROM INFORMATION_SCHEMA.Columns WHERE table_name='" + obj.get("TABLE_NAME") + "' AND table_schema='cxtxdbdev'");


                StringBuffer leisb = new StringBuffer();
                for (int j = 0; j < tables.size(); j++) {
                    //Map<String, Object> t = tables.get(j);
                    leisb.append(ew.tableLieTmpl(table_lie_tmpl, tables.get(j), j));
                }
                obj.put("table_lie_tmpl", leisb.toString());

                tablesb.append(ew.tableTmpl(table_tmpl, list.get(i), i));
           // }
        }

        ew.createWord(tablesb.toString());
        //System.out.println(tablesb.toString());
        System.out.println("================================================================");
    }
}
